package DBLayer;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import ModelLayer.Invoice;

/**
 * @author Gruppe 2
 *
 */
public class DBInvoice implements IFDBInvoice {

	private Connection con;

	DBInvoice() {
		con = DbConnection.getInstance().getDBcon();
	}

	// Done
	
	
	public void insertInvoice(Invoice i) throws SQLException {
		PreparedStatement pstmt = null;
		String insert = "insert into invoice (invoiceNo, paymentDate)"
				+ "values (?,?)";
		System.out.println(insert);
		try {
			pstmt = con.prepareStatement(insert);
			pstmt.setInt(1, i.getInvoiceNo());
			pstmt.setString(2, i.getPaymentDate());
			pstmt.executeQuery();

		} catch (SQLException sqlE) {
			System.out.println("SQL Error");
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			e.getMessage();

		}

	}

	// Done
	
	public Invoice selectSingleInvoice(int id) throws SQLException {
		Invoice i = null;
		String query = "SELECT invoiceNo, paymentDate "
				+ "FROM Invoice WHERE invoiceNo=?";
		ResultSet result = null;
		try {
			PreparedStatement pstmt = con.prepareStatement(query);
			pstmt.setInt(1, id);
			result = pstmt.executeQuery();
			if (result.next()) {
				i = buildInvoice(result);
			}
		} catch (SQLException sqlE) {
			System.out.println("SQL Error");
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			e.getMessage();

		}
		return i;
	}

	@Override
	public void updateInvoice(Invoice i) throws SQLException {
		// TODO Auto-generated method stub
		Invoice iObj = i;

		String update = "UPDATE invoice SET paymentDate = ? WHERE invoiceNo = ?";

		System.out.println(update);
		try {
			PreparedStatement pstmt = null;
			pstmt = con.prepareStatement(update);
			pstmt.setString(1, iObj.getPaymentDate());
			pstmt.setInt(2, iObj.getInvoiceNo());
			pstmt.executeUpdate();

		} catch (SQLException sqlE) {
			System.out.println("SQL Error");
			System.out.println(sqlE.getMessage());
		} catch (Exception e) {
			e.getMessage();

		}

		// update Invoice set .. where invoiceNo = ?

	}


	// Used in : selectSingleInvoice
	private Invoice buildInvoice(ResultSet result) throws SQLException {
		Invoice iObj = new Invoice();

		try {
			iObj.setInvoiceNo(result.getInt("invoiceNo"));
			iObj.setPaymentDate(result.getString("paymentDate"));

		} catch (Exception e) {
			System.out.println("building laaner object");
		}

		return iObj;
	}

}
